﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using com.loto.Common.DBUtility;
using System.Data.SqlClient;
using com.huigo.Framework.Model;

namespace com.huigo.Framework.BLL
{
    public class SalesRelation
    {
        /// <summary>
        /// 添加/修改销售员
        /// </summary>
        /// <param name="custId"></param>
        /// <param name="userId"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        public static bool AddSalesRelation(long custId, long userId,int type,long UserId)
        {
            bool result = false;
            Model.SalesRelation salesRelation = new com.huigo.Framework.Model.SalesRelation();
            using (SqlTransaction trans = SqlHelper.SQLTransaction(SqlHelper.MainSQLConnString))
            {
                if (type == 1)      //直接增加
                {
                    salesRelation.RelationCustFid = custId;
                    salesRelation.RelationUserFid = userId;
                    salesRelation.RelationStartTime = DateTime.Now;
                    salesRelation.RelationCreatedBy = UserId;
                    salesRelation.RelationCreatedOn = DateTime.Now;
                    result = SqlServerDal.SalesRelation.Insert(salesRelation, trans);
                }
                if (type == 2)      //修改
                {
                    Model.SalesRelation sales = new com.huigo.Framework.Model.SalesRelation();
                    sales.RelationCustFid = custId;                    
                    List<string> fileds = new List<string>()
                    {
                        "RELATION_ID,RELATION_CUST_FID,RELATION_USER_FID,RELATION_START_TIME,RELATION_END_TIME"
                    };
                    List<WhereStatement> where = new List<WhereStatement>()
                    {
                        new WhereStatement("RELATION_CUST_FID","=",""+custId+"","")
                    };
                    List<Model.SalesRelation> salesCount = SqlServerDal.SalesRelation.GetItems(fileds, null, where,null,null, trans);
                    if (salesCount.Count == 0)
                    {
                        salesRelation = new com.huigo.Framework.Model.SalesRelation();
                        salesRelation.RelationCustFid = custId;
                        salesRelation.RelationUserFid = userId;
                        salesRelation.RelationStartTime = DateTime.Now;
                        salesRelation.RelationCreatedBy = UserId;
                        salesRelation.RelationCreatedOn = DateTime.Now;
                        result = SqlServerDal.SalesRelation.Insert(salesRelation, trans);
                    }
                    else
                    {
                        Model.SalesRelation relation = new com.huigo.Framework.Model.SalesRelation();
                        for (int i = 0; i < salesCount.Count; i++)
                        {
                            if (Convert.ToDateTime(salesCount[i].RelationEndTime).ToString("yyyy-MM-dd") == "2099-12-31")
                            {
                                relation = salesCount[i];
                            }
                        }
                        if (relation.RelationUserFid != userId)
                        {
                            salesRelation = new com.huigo.Framework.Model.SalesRelation();
                            salesRelation.RelationEndTime = DateTime.Now;
                            List<WhereStatement> whereList = new List<WhereStatement>()
                            {
                                new WhereStatement("RELATION_ID","=",""+relation.RelationId+"","")
                            };
                            bool isTrue = SqlServerDal.SalesRelation.Update(salesRelation, whereList, trans);
                            if (isTrue)
                            {
                                salesRelation = new com.huigo.Framework.Model.SalesRelation();
                                salesRelation.RelationCustFid = custId;
                                salesRelation.RelationUserFid = userId;
                                salesRelation.RelationStartTime = DateTime.Now.AddSeconds(5);
                                salesRelation.RelationCreatedBy = UserId;
                                salesRelation.RelationCreatedOn = DateTime.Now;
                                result = SqlServerDal.SalesRelation.Insert(salesRelation, trans);
                            }
                        }
                    }
                }
                trans.Commit();
            }
            return result;
        }
        /// <summary>
        /// 经销商统计
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <param name="userId"></param>
        /// <returns></returns>
        public static List<Model.SalesRelation> GetSalesRelation(DateTime start, DateTime end, long userId)
        {
            List<Model.SalesRelation> sales = null;
            if (start != DateTime.MinValue && end != DateTime.MinValue)
            {
                //string sql = "select SUM(POLICY_UNIT_PRICE*POLICY_COUNT) AS PRICE,CUST_NAME from(select POLICY_UNIT_PRICE,POLICY_COUNT,RELATION_CUST_FID from (select RELATION_CUST_FID,RELATION_START_TIME,RELATION_END_TIME from HG_SALES_RELATION where (RELATION_START_TIME between '" + start + "' and '" + end + "' or RELATION_END_TIME between '" + start + "' and '" + end + "') and RELATION_USER_FID = " + userId + ")as aa left join(select * from(select POLICY_UNIT_PRICE,POLICY_COUNT,POLICY_ORDER_FID,POLICY_CUST_FID from HG_POLICY_INFO_AIR where POLICY_FEE_STATUS=2 union all select POLICY_UNIT_PRICE,POLICY_COUNT,POLICY_ORDER_FID,POLICY_CUST_FID from HG_POLICY_INFO_TRAVEL where POLICY_FEE_STATUS=2) as c left join HG_ORDER_INFO on ORDER_ID=POLICY_ORDER_FID ) as bb on RELATION_CUST_FID=POLICY_CUST_FID where ORDER_CREATED_ON between RELATION_START_TIME and RELATION_END_TIME) as cc left join HG_CUST_INFO on RELATION_CUST_FID=CUST_ID group by CUST_NAME";

                List<OrderByStatement> orderList = new List<OrderByStatement>()
                {
                    new OrderByStatement("PRICE",false)
                };
                List<string> fileds = new List<string>()
                {
                    " SUM(POLICY_UNIT_PRICE*POLICY_COUNT) AS PRICE,CUST_NAME,CUST_ID from(select POLICY_UNIT_PRICE,POLICY_COUNT,RELATION_CUST_FID from (select RELATION_CUST_FID,RELATION_START_TIME,RELATION_END_TIME from HG_SALES_RELATION where (RELATION_START_TIME between '" + start + "' and '" + end + "' or RELATION_END_TIME between '" + start + "' and '" + end + "') and RELATION_USER_FID = " + userId + ")as aa left join(select POLICY_UNIT_PRICE,POLICY_COUNT,POLICY_CUST_FID,ORDER_CREATED_ON,ORDER_PAID_ON from(select POLICY_UNIT_PRICE,POLICY_COUNT,POLICY_ORDER_FID,POLICY_CUST_FID from HG_POLICY_INFO_AIR where POLICY_FEE_STATUS=2 union all select POLICY_UNIT_PRICE,POLICY_COUNT,POLICY_ORDER_FID,POLICY_CUST_FID from HG_POLICY_INFO_TRAVEL where POLICY_FEE_STATUS=2) as c left join HG_ORDER_INFO on ORDER_ID=POLICY_ORDER_FID ) as bb on RELATION_CUST_FID=POLICY_CUST_FID where (ORDER_CREATED_ON between RELATION_START_TIME and RELATION_END_TIME) and ORDER_PAID_ON < '"+end+"') as cc left join HG_CUST_INFO on RELATION_CUST_FID=CUST_ID group by CUST_NAME,CUST_ID"
                };
                
                sales = SqlServerDal.SalesRelation.GetItemsTemp(fileds, null,null,null,null,null);                
            }           
            
            return sales;
        }
        /// <summary>
        /// 查询明细
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <param name="custFid"></param>
        /// <param name="userId"></param>
        /// <returns></returns>
        public static List<Model.SalesRelation> GetSalesDetail(DateTime start, DateTime end, long custFid,long userId)
        {
            List<string> fileds = new List<string>()
            {
                " ORDER_NO,ORDER_PRODUCT_NAME,(POLICY_UNIT_PRICE*POLICY_COUNT)as PRICE,POLICY_NO,ORDER_CREATED_ON,ORDER_PAID_ON from (select RELATION_CUST_FID,RELATION_START_TIME,RELATION_END_TIME from hg_sales_relation where RELATION_USER_FID="+userId+" and (RELATION_START_TIME between '"+start+"' and '"+end.AddDays(1)+"' or RELATION_END_TIME between '"+start+"' and '"+end.AddDays(1)+"')) a inner join (SELECT aa.*,ORDER_NO,ORDER_PRODUCT_NAME,ORDER_PAID_ON,ORDER_CREATED_ON FROM (select POLICY_NO,POLICY_ORDER_FID,POLICY_CUST_FID,POLICY_UNIT_PRICE,POLICY_COUNT from HG_POLICY_INFO_AIR where POLICY_FEE_STATUS=2 union all select POLICY_NO,POLICY_ORDER_FID,POLICY_CUST_FID,POLICY_UNIT_PRICE,POLICY_COUNT from HG_POLICY_INFO_TRAVEL where POLICY_FEE_STATUS=2) aa,HG_ORDER_INFO WHERE aa.POLICY_ORDER_FID=ORDER_ID) b on a.RELATION_CUST_FID=b.POLICY_CUST_FID AND (b.ORDER_CREATED_ON BETWEEN RELATION_START_TIME AND RELATION_END_TIME and b.ORDER_PAID_ON < '"+end.AddDays(1)+"') where RELATION_CUST_FID="+custFid+""
            };
            List<OrderByStatement> orderList = new List<OrderByStatement>()
            {
                new OrderByStatement("ORDER_CREATED_ON",false)
            };
            List<Model.SalesRelation> sales = SqlServerDal.SalesRelation.GetItemsTemp(fileds, null, null, orderList, null, null);
            return sales;
        }
        /// <summary>
        /// 销售绩效统计
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <param name="userId"></param>
        /// <param name="productName"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        public static List<Model.SalesRelation> GetTotalSales(DateTime start, DateTime end, long userId, string productName, int type)
        {
            List<string> fileds = new List<string>();
            string sql = string.Empty;
            if (type == 1)
            {
                sql = "SUM(POLICY_UNIT_PRICE*POLICY_COUNT) AS PRICE,USER_NAME,USER_ID,ORDER_PRODUCT_NAME,ORDER_PRODUCT_FID from(select POLICY_UNIT_PRICE,POLICY_COUNT,RELATION_USER_FID,ORDER_PRODUCT_NAME,ORDER_PRODUCT_FID from (select RELATION_CUST_FID,RELATION_USER_FID,RELATION_START_TIME,RELATION_END_TIME from HG_SALES_RELATION ";
            }
            else
            {
                sql = "SUM(POLICY_UNIT_PRICE*POLICY_COUNT) AS PRICE,USER_NAME,USER_ID from(select POLICY_UNIT_PRICE,POLICY_COUNT,RELATION_USER_FID,ORDER_PRODUCT_NAME,ORDER_PRODUCT_FID from (select RELATION_CUST_FID,RELATION_USER_FID,RELATION_START_TIME,RELATION_END_TIME from HG_SALES_RELATION ";
            }
            if (start != DateTime.MinValue && end != DateTime.MinValue && userId>0)
            {
                sql += " where (RELATION_START_TIME between '" + start + "' and '" + end.AddDays(1) + "' or RELATION_END_TIME between '" + start + "' and '" + end.AddDays(1) + "') and RELATION_USER_FID = " + userId + ")";
            }
            if (start != DateTime.MinValue && end != DateTime.MinValue && userId == 0)
            {
                sql += " where (RELATION_START_TIME between '" + start + "' and '" + end.AddDays(1) + "' or RELATION_END_TIME between '" + start + "' and '" + end.AddDays(1) + "'))";
            }
            if (start == DateTime.MinValue && end == DateTime.MinValue && userId > 0)
            {
                sql += " where RELATION_USER_FID = " + userId + ")";
            }
            if (start == DateTime.MinValue && end == DateTime.MinValue && userId == 0)
            {
                sql += ")";
            }
            sql += "as aa left join(select POLICY_UNIT_PRICE,POLICY_COUNT,POLICY_CUST_FID,ORDER_CREATED_ON,ORDER_PAID_ON,ORDER_PRODUCT_NAME,ORDER_PRODUCT_FID from(select POLICY_UNIT_PRICE,POLICY_COUNT,POLICY_ORDER_FID,POLICY_CUST_FID from HG_POLICY_INFO_AIR where POLICY_FEE_STATUS=2 union all select POLICY_UNIT_PRICE,POLICY_COUNT,POLICY_ORDER_FID,POLICY_CUST_FID from HG_POLICY_INFO_TRAVEL where POLICY_FEE_STATUS=2) as c left join HG_ORDER_INFO on ORDER_ID=POLICY_ORDER_FID ) as bb on RELATION_CUST_FID=POLICY_CUST_FID where (ORDER_CREATED_ON between RELATION_START_TIME and RELATION_END_TIME ";
            if (end != DateTime.MinValue)
            {
                sql += " and ORDER_PAID_ON < '" + end.AddDays(1) + "'";
            }
            if (!string.IsNullOrEmpty(productName))
            {
                sql += " and ORDER_PRODUCT_FID=" + productName + " ";
            }

            sql += " ))as cc left join HG_USER_INFO on RELATION_USER_FID=USER_ID where USER_ROLE_FIDS like '%," + ((int)Dict.UserRole.Sales) + ",%' group by USER_NAME,USER_ID";
            if (type == 1)
            {
                sql += ",ORDER_PRODUCT_NAME,ORDER_PRODUCT_FID";
            }            

            fileds.Add(sql);
            //if (type == 1)
            //{                
            //    fileds.Add("SUM(POLICY_UNIT_PRICE*POLICY_COUNT) AS PRICE,USER_NAME,ORDER_PRODUCT_NAME from(select POLICY_UNIT_PRICE,POLICY_COUNT,RELATION_USER_FID,ORDER_PRODUCT_NAME from (select RELATION_CUST_FID,RELATION_USER_FID,RELATION_START_TIME,RELATION_END_TIME from HG_SALES_RELATION where (RELATION_START_TIME between '" + start + "' and '" + end + "' or RELATION_END_TIME between '" + start + "' and '" + end + "') and RELATION_USER_FID = " + userId + ")as aa left join(select POLICY_UNIT_PRICE,POLICY_COUNT,POLICY_CUST_FID,ORDER_CREATED_ON,ORDER_PAID_ON,ORDER_PRODUCT_NAME from(select POLICY_UNIT_PRICE,POLICY_COUNT,POLICY_ORDER_FID,POLICY_CUST_FID from HG_POLICY_INFO_AIR where POLICY_FEE_STATUS=2 union all select POLICY_UNIT_PRICE,POLICY_COUNT,POLICY_ORDER_FID,POLICY_CUST_FID from HG_POLICY_INFO_TRAVEL where POLICY_FEE_STATUS=2) as c left join HG_ORDER_INFO on ORDER_ID=POLICY_ORDER_FID ) as bb on RELATION_CUST_FID=POLICY_CUST_FID where (ORDER_CREATED_ON between RELATION_START_TIME and RELATION_END_TIME) and ORDER_PAID_ON < '" + end + "' and ORDER_PRODUCT_NAME='" + productName + "') as cc left join HG_USER_INFO on RELATION_USER_FID=USER_ID group by USER_NAME,ORDER_PRODUCT_NAME");
            //}
            //else
            //{
            //    fileds.Add("SUM(POLICY_UNIT_PRICE*POLICY_COUNT) AS PRICE,USER_NAME from(select POLICY_UNIT_PRICE,POLICY_COUNT,RELATION_USER_FID,ORDER_PRODUCT_NAME from (select RELATION_CUST_FID,RELATION_USER_FID,RELATION_START_TIME,RELATION_END_TIME from HG_SALES_RELATION where (RELATION_START_TIME between '" + start + "' and '" + end + "' or RELATION_END_TIME between '" + start + "' and '" + end + "') and RELATION_USER_FID = " + userId + ")as aa left join(select POLICY_UNIT_PRICE,POLICY_COUNT,POLICY_CUST_FID,ORDER_CREATED_ON,ORDER_PAID_ON,ORDER_PRODUCT_NAME from(select POLICY_UNIT_PRICE,POLICY_COUNT,POLICY_ORDER_FID,POLICY_CUST_FID from HG_POLICY_INFO_AIR where POLICY_FEE_STATUS=2 union all select POLICY_UNIT_PRICE,POLICY_COUNT,POLICY_ORDER_FID,POLICY_CUST_FID from HG_POLICY_INFO_TRAVEL where POLICY_FEE_STATUS=2) as c left join HG_ORDER_INFO on ORDER_ID=POLICY_ORDER_FID ) as bb on RELATION_CUST_FID=POLICY_CUST_FID where (ORDER_CREATED_ON between RELATION_START_TIME and RELATION_END_TIME) and ORDER_PAID_ON < '" + end + "' and ORDER_PRODUCT_NAME='" + productName + "') as cc left join HG_USER_INFO on RELATION_USER_FID=USER_ID group by USER_NAME");
            //}
            
            List<OrderByStatement> orderList = new List<OrderByStatement>()
            {
                new OrderByStatement("PRICE",false)
            };
            List<Model.SalesRelation> saleList = SqlServerDal.SalesRelation.GetItemsTemp(fileds, null, null, orderList, null, null);
            return saleList;
        }
        public static List<Model.SalesRelation> GetSalesDetail(DateTime start, DateTime end, long userId, string productName)
        {
            string sql = "ORDER_NO,ORDER_PRODUCT_NAME,(POLICY_UNIT_PRICE*POLICY_COUNT)as PRICE,POLICY_NO,ORDER_CREATED_ON,ORDER_PAID_ON from (select RELATION_USER_FID,RELATION_CUST_FID,RELATION_START_TIME,RELATION_END_TIME from hg_sales_relation ";
            if (start == DateTime.MinValue && end == DateTime.MinValue && userId > 0)
            {
                sql += " where RELATION_USER_FID=" + userId + ")";
            }
            if (start != DateTime.MinValue && end != DateTime.MinValue && userId > 0)
            {
                sql += " where RELATION_USER_FID=" + userId + " and (RELATION_START_TIME between '" + start + "' and '" + end.AddDays(1) + "' or RELATION_END_TIME between '" + start + "' and '" + end.AddDays(1) + "'))";
            }
            if (start == DateTime.MinValue && end == DateTime.MinValue && userId == 0)
            {
                sql += ")";
            }
            sql += " a inner join (SELECT aa.*,ORDER_NO,ORDER_PRODUCT_NAME,ORDER_PAID_ON,ORDER_CREATED_ON,ORDER_PRODUCT_FID FROM (select POLICY_NO,POLICY_ORDER_FID,POLICY_CUST_FID,POLICY_UNIT_PRICE,POLICY_COUNT from HG_POLICY_INFO_AIR where POLICY_FEE_STATUS=2 union all select POLICY_NO,POLICY_ORDER_FID,POLICY_CUST_FID,POLICY_UNIT_PRICE,POLICY_COUNT from HG_POLICY_INFO_TRAVEL where POLICY_FEE_STATUS=2) aa,HG_ORDER_INFO WHERE aa.POLICY_ORDER_FID=ORDER_ID) b on a.RELATION_CUST_FID=b.POLICY_CUST_FID AND (b.ORDER_CREATED_ON BETWEEN RELATION_START_TIME AND RELATION_END_TIME ";
            
            if (end != DateTime.MinValue)
            {
                sql += "  and b.ORDER_PAID_ON < '2013-5-31')";
            }
            if (end == DateTime.MinValue)
            {
                sql += ")";
            }
            if (!string.IsNullOrEmpty(productName))
            {
                sql += " and ORDER_PRODUCT_FID=" + productName + "";
            }
            

            List<string> fileds = new List<string>();
            fileds.Add(sql);
            List<OrderByStatement> orderList = new List<OrderByStatement>()
            {
                new OrderByStatement("ORDER_CREATED_ON",false)
            };
            List<Model.SalesRelation> sales = SqlServerDal.SalesRelation.GetItemsTemp(fileds, null, null, orderList, null, null);
            return sales;
        }
    }
}
